<?php
namespace app\common\model;

use think\Model;
use think\Db;
class ArchivesMark extends Model
{
    // 指定表名,不含前缀
    protected $name = 'archives_mark';
    /**
     * @param array $arg
     * @param string $field
     * @param string $group_by
     */
    public function getListByArg(array $arg,$field='*',$group_by = null)
    {
        return $this->where($arg)->field($field)->group($group_by)->select();
    }
	
	 public function getListByArgOne(array $arg,$field='*')
    {
        return $this->where($arg)->field($field)->find();
    }

    /**
     * @param $company_name
     * @param int $start_time
     * @param int $end_time
     * @return mixed
     */
    public function getListByCompanyName($company_name,$start_time = 0,$end_time=0){
        $child_plaform = getChildAdminRegByPlaformId($this->plaform);
        //获取档案表
        $group_by = 'DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m")';
        $field = ' count(identity_id),`user_company`,sum(case when pass=1 then 1 else 0 end) as pass_total,sum(case when pass=0 then 1 else 0 end) as no_pass_total,DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m") as year_m ';
        $condition = ['status'=>1,'isdelete'=>0,'user_company'=>$company_name,'admin_reg'=>['in',$child_plaform]];

        if (empty($start_time)) $start_time = time()-3600*12*365;
        if (empty($end_time)) $end_time = time();
        $condition['start_time'] = ['between',[$start_time,$end_time]];
        $total_Company_num = $this->getListByArg($condition,$field,$group_by);
        foreach ($total_Company_num as $key=>$value){
            $companyData['user_company'][]=$value['user_company'];
            $companyData['year_month'][]=$value['year_m'];
            $companyData['pass_total'][]=$value['pass_total'];
            $companyData['no_pass_total'][]=$value['no_pass_total'];
            $companyData['total'][]=($value['pass_total']+$value['no_pass_total']);
            $companyData['throughput_rate'][]=round(sprintf('%.4f', $value['pass_total']/($value['pass_total']+$value['no_pass_total']))*100);//通过率
        }
        $companyData['title'] = ['总人数(单位：人)','未通过人数(单位：人)','通过人数(单位：人)','通过率(单位：%)'];
        return $companyData;
    }

    /**
     * @param $start_time
     * @param $end_time
     * @return mixed
     */
    public function statisticsAllCompany($start_time,$end_time)
    {//获取档案表
        $child_plaform = getChildAdminRegByPlaformId($this->plaform);
        $group_by = 'DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m")';
        $field = ' count(identity_id),count(distinct company_id) as company_total,sum(case when pass=1 then 1 else 0 end) as pass_total,sum(case when pass=0 then 1 else 0 end) as no_pass_total,DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m") as year_m ';
        $condition = ['status'=>1,'isdelete'=>0,'admin_reg'=>['in',$child_plaform]];
        if (empty($start_time)) $start_time = time()-3600*12*365;
        if (empty($end_time)) $end_time = time();
        $condition['start_time'] = ['between',[$start_time,$end_time]];
        $total_Company_num = $this->getListByArg($condition,$field,$group_by);
        foreach ($total_Company_num as $key=>$value){
            $companyData['year_month'][]=$value['year_m'];
            $companyData['company_total'][]=$value['company_total'];//公司数量
            $companyData['pass_total'][]=$value['pass_total'];//通过人数
            $companyData['no_pass_total'][]=$value['no_pass_total'];//未通过人数
            $companyData['total'][]=($value['pass_total']+$value['no_pass_total']);//总人数
            $companyData['throughput_rate'][]=round(sprintf('%.4f', $value['pass_total']/($value['pass_total']+$value['no_pass_total']))*100);//通过率
        }
        $companyData['title'] = ['考试公司数量','总人数(单位：人)','未通过人数(单位：人)','通过人数(单位：人)','通过率(单位：%)'];
        return $companyData;
    }



    /**
     * @param $username
     * @param int $start_time
     * @param int $end_time
     * @return mixed
     */
    public function getListByUserName($realname,$start_time = 0,$end_time=0){
        $child_plaform = getChildAdminRegByPlaformId($this->plaform);
        //获取档案表
        $group_by = 'DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m")';
        $field = ' count(identity_id) as identity_total,avg(user_score) as avg_score,sum(case when pass=1 then 1 else 0 end) as pass_total,sum(case when pass=0 then 1 else 0 end) as no_pass_total,DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m") as year_m ';
        $condition = ['status'=>1,'isdelete'=>0,'realname'=>$realname,'admin_reg'=>['in',$child_plaform]];

        if (empty($start_time)) $start_time = time()-3600*12*365;
        if (empty($end_time)) $end_time = time();
        $condition['start_time'] = ['between',[$start_time,$end_time]];
        $total_User_num = $this->getListByArg($condition,$field,$group_by);
        foreach ($total_User_num as $key=>$value){
            $userData['identity_total'][]=$value['identity_total'];
            $userData['year_month'][]=$value['year_m'];
            $userData['avg_score'][]=$value['avg_score'];
            $userData['pass_total'][]=$value['pass_total'];
            $userData['no_pass_total'][]=$value['no_pass_total'];
            $userData['total'][]=($value['pass_total']+$value['no_pass_total']);
            $userData['throughput_rate'][]=round(sprintf('%.4f', $value['pass_total']/($value['pass_total']+$value['no_pass_total']))*100);//通过率
        }
        $userData['title'] = ['参与考试次数(单位：次)','总次数(单位：次)','未通过次数(单位：次)','通过次数(单位：次)','通过率(单位：%)'];
        return $userData;
    }

    /**
     * @param $start_time
     * @param $end_time
     * @return mixed
     */
    public function statisticsAllUser($start_time,$end_time){
        $child_plaform = getChildAdminRegByPlaformId($this->plaform);
//获取档案表
        $group_by = 'DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m")';
        $field = ' count(identity_id) as identity_total,avg(user_score) as avg_score,sum(case when pass=1 then 1 else 0 end) as pass_total,sum(case when pass=0 then 1 else 0 end) as no_pass_total,DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m") as year_m ';
        $condition = ['status'=>1,'isdelete'=>0,'admin_reg'=>['in',$child_plaform]];
        if (empty($start_time)) $start_time = time()-3600*12*365;
        if (empty($end_time)) $end_time = time();
        $condition['start_time'] = ['between',[$start_time,$end_time]];
        $total_User_num = $this->getListByArg($condition,$field,$group_by);
        foreach ($total_User_num as $key=>$value){
            $userData['year_month'][]=$value['year_m'];
            $userData['identity_total'][]=$value['identity_total'];//参与考试次数
            $userData['pass_total'][]=$value['pass_total'];//通过次数
            $userData['avg_score'][]=$value['avg_score'];//平均成绩
            $userData['no_pass_total'][]=$value['no_pass_total'];//未通过人数
            $userData['total'][]=($value['pass_total']+$value['no_pass_total']);//总人数
            $userData['throughput_rate'][]=round(sprintf('%.4f', $value['pass_total']/($value['pass_total']+$value['no_pass_total']))*100);//通过率
        }
        $userData['title'] = ['参与考试次数(单位：次)','总次数(单位：次)','未通过次数(单位：次)','通过次数(单位：次)','通过率(单位：%)'];
        return $userData;
    }



    /**
     * @param $username
     * @param int $start_time
     * @param int $end_time
     * @return mixed
     */
    public function getListByBasicName($basicname,$start_time = 0,$end_time=0){
        $child_plaform = getChildAdminRegByPlaformId($this->plaform);
        //获取档案表
        $group_by = 'DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m")';
        $field = ' count(distinct identity_id) as identity_total,count(distinct basic_id) as basic_total,basic_id,sum(case when pass=1 then 1 else 0 end) as pass_total,sum(case when pass=0 then 1 else 0 end) as no_pass_total,DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m") as year_m ';
        $condition = ['status'=>1,'isdelete'=>0,'basic_name'=>$basicname,'admin_reg'=>['in',$child_plaform]];
        if (empty($start_time)) $start_time = time()-3600*12*365;
        if (empty($end_time)) $end_time = time();
        $condition['start_time'] = ['between',[$start_time,$end_time]];
        $total_User_num = $this->getListByArg($condition,$field,$group_by);
        $BasicUser = new BasicUser();
        foreach ($total_User_num as $key=>$value){
            $userData['year_month'][]=$value['year_m'];
            $userData['identity_total'][]=$value['identity_total'];//实际参与人数
            $userData['pass_total'][]=$value['pass_total'];//通过次数
            $userData['no_pass_total'][]=$value['no_pass_total'];//未通过次数
            //获取每个考场人数
            $userData['total'][] = $BasicUser->getListByArg(['basic_id'=>$value['basic_id']],'count(identity_id) as num')[0]['num'];
//            $userData['total'][]=($value['pass_total']+$value['no_pass_total']);//总人数
            $userData['throughput_rate'][]=round(sprintf('%.4f', $value['pass_total']/($value['pass_total']+$value['no_pass_total']))*100);//通过率
        }


        $userData['title'] = ['实际参与人数(单位：人)','总人数(单位：人)','未通过次数(单位：次)','通过次数(单位：次)','通过率(单位：%)'];
        return $userData;
    }
    /**
     * @param $start_time
     * @param $end_time
     * @return mixed
     */
    public function statisticsAllBasic($start_time,$end_time){
        $child_plaform = getChildAdminRegByPlaformId($this->plaform);
//获取档案表
        $group_by = 'DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m"),basic_id';
        $field = ' count(distinct identity_id) as identity_total,count(distinct basic_id) as basic_total,basic_id,count(distinct concat(1,identity_id)),sum(case when pass=1 then 1 else 0 end) as pass_total,sum(case when pass=0 then 1 else 0 end) as no_pass_total,DATE_FORMAT(FROM_UNIXTIME(start_time),"%Y-%m") as year_m ';
        $condition = ['status'=>1,'isdelete'=>0,'admin_reg'=>['in',$child_plaform]];
        if (empty($start_time)) $start_time = time()-3600*12*365;
        if (empty($end_time)) $end_time = time();
        $condition['start_time'] = ['between',[$start_time,$end_time]];
        $total_User_num = $this->getListByArg($condition,$field,$group_by);
        $BasicUser = new BasicUser();

        foreach ($total_User_num as $key=>$value){

            $userData['year_month'] =$value['year_m'];
            $userData['basic_total'] +=$value['basic_total'];//考场数量
            $userData['identity_total'] +=$value['identity_total'];//实际参与人数
            $userData['pass_total'] +=$value['pass_total'];//通过次数
            $userData['no_pass_total'] +=$value['no_pass_total'];//未通过次数
            //获取每个考场人数
            $userData['total'] += $BasicUser->getListByArg(['basic_id'=>$value['basic_id']],'count(identity_id) as num')[0]['num'];
//            $userData['total'][]=($value['pass_total']+$value['no_pass_total']);//总人数
            $userData['throughput_rate'] +=round(sprintf('%.4f', $value['pass_total']/($value['pass_total']+$value['no_pass_total']))*100);//通过率
        }
        $userData['title'] = ['实际参与人数(单位：人)','总人数(单位：人)','未通过次数(单位：次)','通过次数(单位：次)','通过率(单位：%)'];
        return $userData;
    }
}
